--1，管理员
create table a (
id integer,
name nvarchar2(50),
pwd nvarchar2(50)
);
--2，学生表
create table s(
id integer,
no integer,
name nvarchar2(50),
pwd nvarchar2(50),
sex nvarchar2(50),
major nvarchar2(50),
B nvarchar2(50),
D nvarchar2(50)
);
--添加学生
insert into S values(id.nextval,sno.nextval,'赵一','111','男','自动化系','A1','A1-101');
insert into S values(id.nextval,sno.nextval,'钱一','111','女','英语系','A1','A1-101');
insert into S values(id.nextval,sno.nextval,'孙一','111','女','通信系','A1','A1-101');
insert into S values(id.nextval,sno.nextval,'李一','111','女','计算机系','A1','A1-101');
insert into S values(id.nextval,sno.nextval,'李一','111','女','计算机系','A1','A1-101');

insert into S values(id.nextval,sno.nextval,'李四','444','男','计算机科学系','A1','A1-102');
insert into S values(id.nextval,sno.nextval,'赵一','111','男','自动化系','A1','A1-102');
insert into S values(id.nextval,sno.nextval,'钱一','111','女','英语系','A1','A1-102');
insert into S values(id.nextval,sno.nextval,'孙一','111','女','通信系','A1','A1-102');
insert into S values(id.nextval,sno.nextval,'李一','111','女','计算机系','A1','A1-102');
insert into S values(id.nextval,sno.nextval,'李一','111','女','计算机系','A1','A1-102');

insert into S values(id.nextval,sno.nextval,'李四','444','男','计算机科学系','A1','A1-103');
insert into S values(id.nextval,sno.nextval,'赵一','111','男','自动化系','A1','A1-103');
insert into S values(id.nextval,sno.nextval,'钱一','111','女','英语系','A1','A1-103');
insert into S values(id.nextval,sno.nextval,'孙一','111','女','通信系','A1','A1-103');
insert into S values(id.nextval,sno.nextval,'李一','111','女','计算机系','A1','A1-103');
insert into S values(id.nextval,sno.nextval,'李一','111','女','计算机系','A1','A1-103');

insert into S values(id.nextval,sno.nextval,'李四','444','男','计算机科学系','A1','A1-104');
insert into S values(id.nextval,sno.nextval,'赵一','111','男','自动化系','A1','A1-104');
insert into S values(id.nextval,sno.nextval,'钱一','111','女','英语系','A1','A1-104');
insert into S values(id.nextval,sno.nextval,'孙一','111','女','通信系','A1','A1-104');
insert into S values(id.nextval,sno.nextval,'李一','111','女','计算机系','A1','A1-104');
insert into S values(id.nextval,sno.nextval,'李一','111','女','计算机系','A1','A1-104');

insert into S values(id.nextval,sno.nextval,'李四','444','男','计算机科学系','A1','A1-105');
insert into S values(id.nextval,sno.nextval,'赵一','111','男','自动化系','A1','A1-105');
insert into S values(id.nextval,sno.nextval,'钱一','111','女','英语系','A1','A1-105');
insert into S values(id.nextval,sno.nextval,'孙一','111','女','通信系','A1','A1-105');
insert into S values(id.nextval,sno.nextval,'李一','111','女','计算机系','A1','A1-105');
insert into S values(id.nextval,sno.nextval,'李一','111','女','计算机系','A1','A1-105');

insert into S values(id.nextval,sno.nextval,'李四','444','男','计算机科学系','A1','A1-106');
insert into S values(id.nextval,sno.nextval,'赵一','111','男','自动化系','A1','A1-106');
insert into S values(id.nextval,sno.nextval,'钱一','111','女','英语系','A1','A1-106');
insert into S values(id.nextval,sno.nextval,'孙一','111','女','通信系','A1','A1-106');
insert into S values(id.nextval,sno.nextval,'李一','111','女','计算机系','A1','A1-106');
insert into S values(id.nextval,sno.nextval,'李一','111','女','计算机系','A1','A1-106');

insert into S values(id.nextval,sno.nextval,'李四','444','男','计算机科学系','A1','A1-107');
insert into S values(id.nextval,sno.nextval,'赵一','111','男','自动化系','A1','A1-107');
insert into S values(id.nextval,sno.nextval,'钱一','111','女','英语系','A1','A1-107');
insert into S values(id.nextval,sno.nextval,'孙一','111','女','通信系','A1','A1-107');
insert into S values(id.nextval,sno.nextval,'李一','111','女','计算机系','A1','A1-107');
insert into S values(id.nextval,sno.nextval,'李一','111','女','计算机系','A1','A1-107');

insert into S values(id.nextval,sno.nextval,'李四','444','男','计算机科学系','A1','A1-108');
insert into S values(id.nextval,sno.nextval,'赵一','111','男','自动化系','A1','A1-108');
insert into S values(id.nextval,sno.nextval,'钱一','111','女','英语系','A1','A1-108');
insert into S values(id.nextval,sno.nextval,'孙一','111','女','通信系','A1','A1-108');
insert into S values(id.nextval,sno.nextval,'李一','111','女','计算机系','A1','A1-108');
insert into S values(id.nextval,sno.nextval,'李一','111','女','计算机系','A1','A1-108');

--3，教师表
create table t(
id integer,
no integer,
name nvarchar2(50),
pwd nvarchar2(50),
sex nvarchar2(50),
tel nvarchar2(50)
);

alter table T add building varchar(20);
update T set building='A1' where no=20001;
update T set building='A2' where no=20002;
update T set building='A3' where no=20021;

--4，宿舍表
create table d(
id integer,
name nvarchar2(50),
type nvarchar2(50),
pup nvarchar2(50)
);
--添加宿舍记录
select * from D t

insert into D values(id.nextval,'A1-101','6',6);
insert into D values(id.nextval,'A1-102','6',6);
insert into D values(id.nextval,'A1-103','6',6);
insert into D values(id.nextval,'A1-104','6',6);
insert into D values(id.nextval,'A1-105','6',6);
insert into D values(id.nextval,'A1-106','6',6);
insert into D values(id.nextval,'A1-107','6',6);
insert into D values(id.nextval,'A1-108','6',6);
insert into D values(id.nextval,'A1-109','6',6);
insert into D values(id.nextval,'A1-110','6',6);
insert into D values(id.nextval,'A1-111','6',6);
insert into D values(id.nextval,'A1-112','6',6);
insert into D values(id.nextval,'A1-113','6',6);
insert into D values(id.nextval,'A2-201','6',6);
insert into D values(id.nextval,'A2-202','6',6);
insert into D values(id.nextval,'A2-203','6',6);
insert into D values(id.nextval,'A2-204','6',6);
insert into D values(id.nextval,'A2-205','6',6);
insert into D values(id.nextval,'A2-206','6',6);
insert into D values(id.nextval,'A2-207','6',6);
insert into D values(id.nextval,'A2-208','6',6);
insert into D values(id.nextval,'A2-209','6',6);
insert into D values(id.nextval,'A2-210','6',6);
insert into D values(id.nextval,'A2-211','6',6);
insert into D values(id.nextval,'A2-212','6',6);
insert into D values(id.nextval,'A2-213','6',6);
insert into D values(id.nextval,'A3-301','6',6);
insert into D values(id.nextval,'A3-302','6',6);
insert into D values(id.nextval,'A3-303','6',6);
insert into D values(id.nextval,'A3-304','6',6);
insert into D values(id.nextval,'A3-305','6',6);
insert into D values(id.nextval,'A3-306','6',6);
insert into D values(id.nextval,'A3-307','6',6);
insert into D values(id.nextval,'A3-308','6',6);
insert into D values(id.nextval,'A3-309','6',6);
insert into D values(id.nextval,'A3-310','6',6);
insert into D values(id.nextval,'A3-311','6',6);
insert into D values(id.nextval,'A3-312','6',6);
insert into D values(id.nextval,'A3-313','6',6);

--5，楼栋表
create table b(
id integer,
name nvarchar2(50),
thing nvarchar2(50)
)
--插入楼栋信息
insert into B values(id.nextval,'A1',null);
insert into B values(id.nextval,'A2',null);
insert into B values(id.nextval,'A3',null);
insert into B values(id.nextval,'A4',null);
insert into B values(id.nextval,'A5',null);
insert into B values(id.nextval,'A6',null);
insert into B values(id.nextval,'A7',null);
insert into B values(id.nextval,'A8',null);
insert into B values(id.nextval,'A9',null);
insert into B values(id.nextval,'A10',null);
insert into B values(id.nextval,'A11',null);
insert into B values(id.nextval,'A12',null);
insert into B values(id.nextval,'A13',null);

insert into B values(id.nextval,'B1',null);
insert into B values(id.nextval,'B2',null);

insert into B values(id.nextval,'C1',null);
insert into B values(id.nextval,'C2',null);
insert into B values(id.nextval,'C3',null);
insert into B values(id.nextval,'C4',null);
insert into B values(id.nextval,'C5',null);
insert into B values(id.nextval,'C6',null);


--6，上报的晚归的学生的记录给学校，若学校审批通过，则直接从该表中将相应的记录转移到ce表中，若学校不通过，
--则仅仅把相应学生的记录从该表中删除。
create table cu(
id integer,
time nvarchar2(50),
no integer,
name nvarchar2(50),
major nvarchar2(50),
thing nvarchar2(50),
d nvarchar2(50),
t nvarchar2(50)
);
--添加上表的數據
insert into CU values(id.nextval,'2017-6-14',10004,'李四','计算机科学系','未归原因','A1-102','李老师');
insert into CU values(id.nextval,'2017-6-14',10005,'赵一','自动化系','未归原因','A1-102','斯蒂芬森');
insert into CU values(id.nextval,'2017-6-14',10004,'钱一','英语系','未归原因','A1-102','李老师');
insert into CU values(id.nextval,'2017-6-14',10004,'孙一','通信系','未归原因','A1-102','李老师');
insert into CU values(id.nextval,'2017-6-14',10004,'李四','计算机科学系','未归原因','A1-102','李老师');

--7
create table ce(
id integer,
time nvarchar2(50),
no integer,
name nvarchar2(50),
major nvarchar2(50),
thing nvarchar2(50),
d nvarchar2(50),
t nvarchar2(50)
)
--
create sequence id minvalue 1 maxvalue 9999999 increment by 1 start with 1;
create sequence sno minvalue 1 maxvalue 99999 increment by 1 start with 10001;
create sequence tno minvalue 1 maxvalue 99999 increment by 1 start with 20001;


--上传的报修表，学生寝室设备损坏，向宿管上传的信息
create table addmaintaining(
id integer,
time nvarchar2(50),
no integer,
name nvarchar2(50),
d nvarchar2(50),
thing nvarchar2(50),
remark nvarchar2(50)
)

--已处理的报修表，宿管根据处理结果留下的记录
create table maintainingresult(
id integer,
time nvarchar2(50),
no integer,
name nvarchar2(50),
d nvarchar2(50),
thing nvarchar2(50),
result nvarchar2(50),
resulttime nvarchar2(50),
remark nvarchar2(50)
)
